from openpyxl import Workbook,load_workbook
from copy import copy

def merge_to_one(file_path,ws):
    wb1 = load_workbook(file_path)
    ws1 = wb1.active
    # 写到新的excel中，每个表作为一个worksheet，复制表样式
    max_row = ws1.max_row
    max_column = ws1.max_column
    for i in range(1, max_row + 1):
        for j in range(1, max_column + 1):
            cell = ws.cell(row=i, column=j)  # 新的单元格
            load_cell = ws1.cell(row=i, column=j)  # 加载excel数据的单元格
            cell.value = load_cell.value
            cell.font = copy(load_cell.font)
            cell.fill = copy(load_cell.fill)
            cell.border = copy(load_cell.border)
            cell.number_format = copy(load_cell.number_format)
    ws.column_dimensions['A'].width = 15
    ws.column_dimensions['G'].width = 15


if __name__ == '__main__':
    wb = Workbook()
    ws_huadong = wb.create_sheet('华东', 0)
    ws_huanan = wb.create_sheet('华南', 1)
    ws_huabei = wb.create_sheet('华北', 2)
    #调用函数
    merge_to_one('../data/表1-华东.xlsx',ws_huadong)
    merge_to_one('../data/表2-华南.xlsx',ws_huanan)
    merge_to_one('../data/表3-华北.xlsx',ws_huabei)
    wb.save('综合数据.xlsx')
    wb.close()